﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MISAPP.Classify.DataAccess;

namespace MISAPP.Classify.Service
{
    public class Service_Squared:Service_Matrix
    {
        protected DA_Squared da = null;
        protected DA_Squared DA
        { 
            get
            {
                if (da == null)
                {
                    da = new DA_Squared();
                }
                return da;
            }
        }

        public Service_Squared(string p_team)
        {
            team = p_team;
        }

        public Service_Squared() { }


        public List<Squared> GetSquared(string p_level)
        {
            return GetParameterValue(DA, new object[] { p_level,team});
        }

        protected DataTable getSquared(string p_para)
        {
            return DA.GetSquaredAreaByLevel(p_para);
        }

        /// <summary>
        /// 稅前淨利彙總對應的BU
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, string> GetSquaredCaption()
        {
            //Dictionary<string, string> captionDict = new Dictionary<string, string>();
            DataTable dt = getSquared("Z");
            //C_銀行,E_證券,G_人壽,I_產險,K_AMC,M_92%創投,O_管顧,Q_直效,S_金控,U_75%FBHK,W_51%運彩,Y_40%投信,AC_double booking_
            //string[] captions = dt.Rows[0]["fldvalue"].ToString().Split(new char[]{','});
            //foreach (string caption in captions)
            //{
            //    string[] c1 = caption.Split(new char[] { '_' });
            //    captionDict.Add(c1[0], c1[1]);
            //}
            return parseSquaredToDictionary(dt);
        }

        /// <summary>
        /// 取得其它收支要對應的BU
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, string> GetSquaredOthenCaption()
        {
            DataTable dt = getSquared("Y");
            return parseSquaredToDictionary(dt);
        }

        protected Dictionary<string, string> parseSquaredToDictionary(DataTable p_dt)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            string[] captions = p_dt.Rows[0]["fldvalue"].ToString().Split(new char[] { ',' });
            foreach (string caption in captions)
            {
                string[] c1 = caption.Split(new char[] { '_' });
                dict.Add(c1[0], c1[1]);
            }
            return dict;        
        }

        public bool InsertSquared(List<Squared> p_squared,string p_user)
        {
            bool ok = false;
            try
            {
                foreach (Squared sq in p_squared)
                {
                    if (false == DA.Insert(sq.MISGroup, sq.MISItem, sq.Amount
                        , p_user, sq.RowPos, sq.Sheet, sq.Comment))
                    {
                        ok = false;
                        throw new Exception("新增失敗");
                    }
                }
                ok = true;
            }
            catch (Exception ex)
            {
                ok = false;
                throw new Exception("新增失敗");
            }
            return ok;
        }


        public DataTable GetSquaredDT()
        {
            return DA.GetSquaredDT();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool InsertSquared(DataTable p_dt)
        {
            bool ok = false;
            try
            {
                ok = true;
                DA.Insert(p_dt);
            }
            catch (Exception ex)
            {
                ok = false;
                throw new Exception(ex.Message);                
            }            
            return ok;
        }

        /// <summary>
        /// 利用陣列把內容一次全匯入
        /// </summary>
        /// <param name="p_lines"></param>
        /// <param name="p_user"></param>
        /// <returns></returns>
        public bool InsertSquared(string[] p_lines, string p_user)
        {
            bool ok = false;
            DataTable dt = DA.GetSquaredDT();
            DataRow row = null;
            char[] separator = { '!'};
            foreach (string line in p_lines)
            {
                string[] cells = line.Split(separator);                
                for (int i = 1; i < cells.Length; i+=2)
                {
                    row = dt.NewRow();
                    row["MISGroup"] = cells[i];
                    row["MISItem"] = string.Empty;
                    row["Amount"] = cells[0].IndexOf("數") > 0 ? Convert.ToDecimal(cells[i + 1] + "0") : 0;
                    row["user_id"] = p_user;
                    row["ROW_POS"] = getRow(cells[i]);
                    row["SHEETNAME"] = cells[0];
                    row["COMMENT"] = cells[0].IndexOf("數") > 0 ? "" : cells[i + 1];
                    dt.Rows.Add(row);                
                }
            }
            //insert to db
            DA.Insert(dt);
            return ok;
        }

        public DataTable GetDataBySheetName(string p_sheet,string p_other,string p_after)
        {
            return DA.GetDataBySheet(p_sheet,p_other,p_after);
        }
        
        public DataTable GetDataBySheetName(string p_sheet, string p_other)
        {
            return DA.GetDataBySheet(p_sheet, p_other, string.Empty);
        }

        public string[] GetCriteriaArray()
        {
            DA_UserInfo usr = new DA_UserInfo();
            return usr.GetDistinctField();
        }

        public DataSet GetCriteriaData()
        {
            DA_UserInfo usr = new DA_UserInfo();
            return usr.GetCriteria();
        }

        public bool UpdateParameter(string p_level, string p_sheetName,string p_fldValue)
        {
            DA_MISParameter para = new DA_MISParameter();
            return para.Update( "SQUARED",  p_level,  p_sheetName,p_fldValue);
        }

        public DataTable Squared2Excel(System.Collections.ArrayList p_criteria)
        {
            System.Collections.ArrayList al1 = p_criteria[0] as
               System.Collections.ArrayList;
            DataTable dt = null;
            if (al1[5].ToString().Equals("GetBonusData"))
            {
                dt = GetBonusData(al1[1].ToString());
            }
            else
            {
                System.Reflection.MethodInfo mi = DA.GetType().GetMethod(al1[5] as string);
                List<object> paraOjbect = new List<object>();
                if (mi.GetParameters().Length > 0)
                {
                    paraOjbect.Add(al1[1] as string);
                    paraOjbect.Add(al1[2] as string);
                }
                //DataTable dt = mi.Invoke(DA, new object[] {  }) as DataTable;
                dt = mi.Invoke(DA, paraOjbect.ToArray()) as DataTable;            
            }
            return dt;
        }

        /// <summary>
        /// 把cell的位置轉成row ex:A18→18
        /// </summary>
        /// <param name="p_rng"></param>
        /// <returns></returns>
        protected int getRow(string p_rng)
        {
            int row = 0;
            for (int i = 1; i < p_rng.Length; i++)
            {
                bool result = Int32.TryParse(p_rng.Substring(i), out row);
                if (result)
                { break; }
            }
            return row;
        }

        public void FillSquared2Xls(string p_xls)
        { 
            //@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
            DA.FillSquaredData(p_xls);
        }

        public bool InsertFromXls(string p_xls, string p_level,string p_id)
        {
            DA_Bonus bonus = new DA_Bonus();
            return DA.InsertFromXls(p_xls, p_level,p_id,bonus.GetBonusEmptyTable(),"squared");
        }

        public DataTable GetBonusData(string p_team)
        {
            Service_Bonus bon = new Service_Bonus();            
            return bon.GetDataBySheet(Utility.GetSheets(p_team));;
        }

    }
}
